CREATE OR REPLACE PROCEDURE public.p_networth_calculation(IN accounts_split text)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
	rec_account record;
	rec_fund record;
	rec_sumary record;
	text_exception text;
	tranDate_ary varchar(10)[] := '{}'; -- 交易日数组
	vol_ary numeric(20,2)[] := '{}'; -- 日份额数组 
	nav_ary numeric(8,4)[] := '{}'; -- 日净值数组
	vol_pre numeric(20,2) := 0; -- 上日份额
	nav_pre numeric(8,4) := 1; -- 上日净值
	sums jsonb :='{}';
BEGIN
	for rec_account in 
		select id
		, coalesce((json_data->>'nav_last')::numeric,1) nav_last
		, coalesce((json_data->>'nav_min')::numeric,1) nav_min
		, coalesce((json_data->>'nav_max')::numeric,1) nav_max
		, coalesce((json_data->>'vol_last')::numeric,0) vol_last
		, coalesce((json_data->>'trans_begin'),'') trans_begin
		, coalesce((json_data->>'trans_end'),'') trans_end
		, coalesce((json_data->>'fund_net')::numeric,0) fund_net
		, coalesce((json_data->>'fund_profit')::numeric,0) fund_profit
		, coalesce((json_data->>'begin_fund')::numeric,0) begin_fund
		, coalesce((json_data->>'back_max')::numeric,0) back_max
		, coalesce((json_data->>'back_max_date'),'') back_max_date
		, coalesce(json_data->'nav_list','[]') nav_list
		from tr_account where id in(select (unnest(string_to_array(accounts_split, ',')))::int4)
		order by id
	loop
	  tranDate_ary:='{}';
	  vol_ary:='{}';
	  nav_ary:='{}';
	  vol_pre:=rec_account.vol_last;
	  nav_pre:=rec_account.nav_last;
	  --RAISE INFO '开始处理 %' , rec_account;
	  FOR rec_fund IN 
	  	SELECT t.tran_date,t.settle_posit,t.presettle_posit,t.deposit,t.withdraw,t.close_profit,t.position_profit,t.commission,t.margin
		 , 0::numeric(20,2) as vol, 0::numeric(8,4) as nav
		 from tr_day_statis t 
		 where t.account_id=rec_account.id 
		 and tran_date>COALESCE(rec_account.trans_end,'')
		 order by t.tran_date -- 假设有一个id列用于指定行的顺序

	  LOOP
	  	
		IF jsonb_array_length(rec_account.nav_list)=0 then -- 是新导入的账单
			rec_account.trans_begin=rec_fund.tran_date;
			rec_account.fund_net:=0;
			rec_account.fund_profit:=0;
			vol_pre := case when rec_fund.presettle_posit>0 then rec_fund.presettle_posit else 0 end;
			rec_account.begin_fund:= case 
				when rec_fund.presettle_posit=0 then 
					case when rec_fund.deposit-rec_fund.withdraw=0 then rec_fund.settle_posit else rec_fund.deposit-rec_fund.withdraw end
				else rec_fund.presettle_posit end;
		END IF;
		rec_fund.vol := vol_pre+((rec_fund.deposit-rec_fund.withdraw)/nav_pre);
		rec_account.fund_net:=rec_account.fund_net+rec_fund.deposit-rec_fund.withdraw;
		rec_account.fund_profit:=rec_account.fund_profit+rec_fund.close_profit+rec_fund.position_profit-rec_fund.commission;
		
		--RAISE INFO '%-%-%' , rec_fund.tran_date,rec_fund.vol,nav_pre;
		rec_fund.nav := case when rec_fund.vol=0 or round(rec_fund.settle_posit/rec_fund.vol,4)=0 then nav_pre else round(rec_fund.settle_posit/rec_fund.vol,4) end;
		-- 考虑到一些离场后还有一些小值权益
		rec_fund.nav := case when rec_fund.nav<0.45 then 1 else rec_fund.nav end;
		rec_account.nav_min=least(rec_account.nav_min, rec_fund.nav);
		rec_account.nav_max=greatest(rec_account.nav_max, rec_fund.nav);
		IF rec_account.back_max<round((rec_account.nav_max-rec_fund.nav)/rec_account.nav_max,4) then
			rec_account.back_max=round((rec_account.nav_max-rec_fund.nav)/rec_account.nav_max,4);
			rec_account.back_max_date=rec_fund.tran_date;
		END IF;
		
		vol_pre :=rec_fund.vol;
		nav_pre :=rec_fund.nav;
		tranDate_ary := tranDate_ary||rec_fund.tran_date;
		vol_ary := vol_ary||rec_fund.vol;
		rec_account.nav_list:=jsonb_insert(rec_account.nav_list, '{-1}', to_jsonb(rec_fund.nav), true);
		-- RETURN QUERY SELECT vol_ary, nav_ary; -- 返回数组
		-- RAISE INFO '%【% ~ %】' , rec_fund.tran_date, rec_fund.vol, rec_fund.nav;
	  END LOOP;
-- 	  RAISE INFO '%-%' , to_jsonb(tranDate_ary),to_jsonb(nav_ary);
-- 	  RAISE INFO '%' , nav_ary;
	  --/*
	  if array_length(tranDate_ary,1)>0 then
-- 	  	RAISE INFO 'days:%' , tranDate_ary;
-- 		RAISE INFO 'navs:%' , rec_account.nav_list;
	  	-- 计算其它字段
		select
-- 		round(stddev(nav_grows),4) nav_std
-- 		,round((POW(1+AVG(nav_grows),250)-1-0.03)/stddev(nav_grows)/POW(250,0.5),2) sharpe_ratio
-- 		,round((POW(1+AVG(nav_grows),250)-1-0.03)/stddev(case when nav_grows<0 then nav_grows else 0 end)/POW(250,0.5),2) sort_ratio
-- 		,round(SUM(case when nav_grows<0 then 0 else 1 end)/COUNT(id)::numeric*ABS((SUM(case when nav_grows<0 then 0 else nav_grows end)/SUM(case when nav_grows<0 then 0 else 1 end))/(SUM(case when nav_grows<0 then nav_grows else 0 end)/SUM(case when nav_grows<0 then 1 else 0 end))),2) win_profit_rate
		AVG(nav_grows) growAvg
		,STDDEV(nav_grows) growStd
		,STDDEV(case when nav_grows<0 then nav_grows else 0 end) loseStd
		,COUNT(id) totals
		,SUM(case when nav_grows<0 then 0 else 1 end) winCnts
		,SUM(case when nav_grows<0 then 0 else nav_grows end) wins
		,SUM(case when nav_grows<0 then nav_grows else 0 end) loses
		,0 as win_profit_rate
		,-1 as sharpe_ratio
		,-1 as sort_ratio
		into rec_sumary
		from(
			select id,nav,coalesce(round((nav-lag(nav) over(order by tran_date))/(lag(nav) over(order by tran_date)),4),0) as nav_grows
			from (select rec_account.id, unnest(tranDate_ary) as tran_date, jsonb_array_elements(rec_account.nav_list)::numeric as nav) a
		) t group by id;
		
-- 		RAISE INFO '%' , to_json(rec_sumary);
		rec_sumary.growStd=round(rec_sumary.growStd,4);
-- 		rec_sumary.win_profit_rate=round(rec_sumary.winCnts/rec_sumary.totals*ABS((rec_sumary.wins/rec_sumary.winCnts)/(rec_sumary.loses/(rec_sumary.totals-rec_sumary.winCnts))), 2);
		-- 防止除零判断
		if rec_sumary.wins=0 then rec_sumary.win_profit_rate=0;	end if;
		if rec_sumary.loses=0 then rec_sumary.win_profit_rate=100;	end if;
		if rec_sumary.growStd!=0 and rec_sumary.loseStd!=0 then
			rec_sumary.sharpe_ratio=round((POW(1+rec_sumary.growAvg,250)-1-0.03)/rec_sumary.growStd/POW(250,0.5),2);
			rec_sumary.sort_ratio=round((POW(1+rec_sumary.growAvg,250)-1-0.03)/rec_sumary.loseStd/POW(250,0.5),2);
		end if;
		--RAISE INFO '开始处理 %' , rec_account.id;
		 update tr_account 
		 set json_data=json_data||jsonb_build_object(
		    'trans_begin', rec_account.trans_begin
		  , 'trans_end', rec_fund.tran_date
		  , 'settle_posit', rec_fund.settle_posit
	, 'close_profit', rec_fund.close_profit
	, 'position_profit', rec_fund.position_profit
	, 'commission', rec_fund.commission
	, 'margin', rec_fund.margin
		  , 'begin_fund', rec_account.begin_fund
		  , 'fund_net', rec_account.fund_net
		  , 'fund_profit', rec_account.fund_profit
		  , 'back_max', rec_account.back_max
		  , 'back_max_date', rec_account.back_max_date
		  , 'nav_last', rec_fund.nav
		  , 'nav_min', rec_account.nav_min
		  , 'nav_max', rec_account.nav_max
		  , 'vol_last', rec_fund.vol
		  , 'nav_ratio', rec_sumary.growStd
		  , 'sharpe_ratio', rec_sumary.sharpe_ratio
		  , 'sort_ratio', rec_sumary.sort_ratio
		  , 'win_profit_rate', rec_sumary.win_profit_rate
		  --, 'nav_list', rec_account.nav_list
		 ) where id=rec_account.id;
		
		-- 更新敞口数据到日统计表 tr_day_statis
		UPDATE tr_day_statis SET open_interest=t.holdvalue
		from (select vh.tran_date,sum((case when vh.direction='卖' then -1 else 1 end)*vh.marketvalue) holdvalue from vw_hold vh 
		WHERE account_id=rec_account.id AND tran_date>COALESCE(rec_account.trans_end,'')
		group by vh.tran_date) t
		WHERE tr_day_statis.account_id=rec_account.id AND tr_day_statis.tran_date=t.tran_date;
	
	
	  end if;
	END LOOP;
END $procedure$
;
